const { db, sqlExec } = require('../db/mysql57');


const punchcardDao = {
    /*
     * 用户签到
     */
    async signIn(uid) {
        let sql = `INSERT INTO PunchCard (uid) (
                SELECT ? WHERE (
                    SELECT COUNT(*)
                    FROM PunchCard
                    WHERE uid = ?
                        AND DATE(time) = CURRENT_DATE()
                ) = 0
            )`;
        let data = [ uid, uid ];
        return sqlExec(sql, data);
    },
    /*
     * 检查用户当天是否已经签到
     */
    async checkSigned(uid) {
        let sql = `SELECT
                COUNT(*) as signed
            FROM PunchCard
            WHERE
                uid = ?
                AND DATE(time) = CURRENT_DATE() LIMIT 1`;
        let data = [ uid ];
        return sqlExec(sql, data);
    },
    /*
     * 查询当月已签到日期
     */
    async getSignedDays(uid) {
        let sql = `
            SELECT DATE_FORMAT(time, '%Y-%m-%d') as signedDate
            FROM PunchCard
            WHERE uid = ?
                AND MONTH(time) = MONTH(CURRENT_DATE())
                AND YEAR(time) = YEAR(CURRENT_DATE());
        `;
        let data = [ uid ];
        return sqlExec(sql, data);
    },
    /*
     * 获取所有的打卡信息
     */
    async getPunchcard(uid, page) {
        let sql = `select
                p.id as id,
                u.username as username,
                u.id as uid,
                u.openid as openid,
                DATE_FORMAT(p.time, '%Y-%m-%d %H:%i:%s') as time
            from PunchCard as p
            left join User as u on p.uid = u.id
            order by p.time desc
            limit ?, 50`;
        let data = [ (page-1) * 50 ];
        return sqlExec(sql, data);
    }
};

module.exports = punchcardDao;